package com.jcb.countfrench.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.jcb.countfrench.model.User;

public abstract class GlobalDAO extends SQLiteOpenHelper {
public static final String DB_NAME = "db" ; 

public GlobalDAO(Context context) {
		super(context, DB_NAME, null, 1);
		
	}

public abstract void insertInitialData(SQLiteDatabase db) ;

@Override
public void onCreate(SQLiteDatabase db) {
	// TODO Auto-generated method stub
//	Log.i("GlobalDAO", "CREATE TABLE type_quizz");
//	db.execSQL("CREATE TABLE type_quizz (id_type_quizz INTEGER PRIMARY KEY, lib_type_quizz TEXT);");
//	
	Log.i("GlobalDAO", "CREATE TABLE quizz");
	db.execSQL("CREATE TABLE quizz (id_quizz INTEGER PRIMARY KEY,lib_quizz TEXT NOT NULL, id_type_quizz INTEGER NOT NULL, ordre INTEGER NOT NULL);");

	Log.i("GlobalDAO", "CREATE TABLE quizz_user");
	db.execSQL("CREATE TABLE quizz_user (id_quizz INTEGER, user TEXT,  actif INTEGER);");
	
	//	
//	//int dum = 0 ;
	Log.i("GlobalDAO", "CREATE TABLE quizz_result");
	db.execSQL("CREATE TABLE quizz_result (id_quizz INTEGER, user TEXT,  note INTEGER);");
	
	Log.i("GlobalDAO", "CREATE TABLE quizz_question");
	db.execSQL("CREATE TABLE quizz_question (id_question INTEGER,id_quizz INTEGER, ordre INTEGER, proposition1 TEXT, proposition2 TEXT, proposition3 TEXT, proposition4 TEXT,solution TEXT, phon_trad TEXT  );");
	
	Log.i("GlobalDAO", "CREATE TABLE dictionary");
	db.execSQL("CREATE TABLE dictionary (id_dictionary INTEGER, ordre INTEGER,num_dictionary TEXT, mot_langue_ref TEXT, mot_trad TEXT,phon_langue_ref TEXT,phon_trad TEXT" +
			", traduction TEXT, example TEXT, sound INTEGER, sound_trad INTEGER );");

	
//	Log.i("GlobalDAO", "CREATE TABLE quizz_question_result");
//	db.execSQL("CREATE TABLE quizz_question_result (id_quizz INTEGER, user TEXT,  valeur TEXT, reponseUser TEXT);");

	//feedData(db);
	insertInitialData(db);
}

public void createTable() {
	SQLiteDatabase db = this.getWritableDatabase();
	onCreate(db);
}




public void dropOneTable(SQLiteDatabase db, String table) {
	try {
		Log.i("GlobalDAO", "deb DROP TABLE " + table);
		db.execSQL("DROP TABLE  " + table + " ;");
		Log.i("GlobalDAO", "fin DROP TABLE " + table);
	}
	catch (Exception e) {
		Log.i("GlobalDAO", "erreur DROP TABLE dictionary ...");
	}
	
}

public void dropTable() {
	Log.i("GlobalDAO", "drop tables ...");
	SQLiteDatabase db = this.getWritableDatabase();
	//dropOneTable(db, "quizz_question_result");
	dropOneTable(db, "quizz_result");
	dropOneTable(db, "quizz_question");
	dropOneTable(db, "quizz");
//	dropOneTable(db, "type_quizz");
	dropOneTable(db, "dictionary");	
	dropOneTable(db, "quizz_user");		
	Log.i("GlobalDAO", "fin drop tables ...");
}


public int getMaxNumQuestion(int idLearning) {
	int max = 0 ;
	Log.i("GlobalDAO", "getMaxNumQuestion ...");
	SQLiteDatabase db = this.getWritableDatabase();
	String requete = "select  max(num_dictionary)  "
	+ " FROM dictionary "
	+ " WHERE id_dictionary =" + idLearning ;
	;	
	Cursor cursor = db.rawQuery(requete, null);
	if (cursor.moveToNext()) {
		max = cursor.getInt(0) ;
	}
	Log.i("GlobalDAO", "getMaxNumQuestion " + max);
	return max ; 
}






	
	public void recordAnswerUser(Integer idQuizz, String valeur, String reponseUser,User user) {
		Log.i("GlobalDAO", "recordAnswerUser debut");
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("valeur", valeur);
		cv.put("reponseUser", reponseUser);
		cv.put("user", user.getUserName());
		String[] args = { String.valueOf(idQuizz) , user.getUserName()} ;
		
		int nbRows = db.update("quizz_question_result",cv, " id_quizz=? and user=?" , args );
		//pas d'update alors on insert
		if (nbRows == 0) {
			cv.put("user", user.getUserName());
			cv.put("id_quizz", String.valueOf(idQuizz));
			long rowid = db.insert("quizz_question_result", "title", cv);
			Log.i("GlobalDAO", "rowid=" + rowid);
		}
		
		
		Log.i("GlobalDAO", "Nb rows updated=" + nbRows);
	}
	
	public void readTables() {
		Log.i("GlobalDAO", "readTables - deb"  );
		SQLiteDatabase db = this.getWritableDatabase();
		String requete = "select id_dictionary" +
				",mot_langue_ref " +
				",phon_langue_ref" +
				",ordre,phon_langue_ref" +
				"  FROM dictionary order by num_dictionary";
		Cursor cursor = db.rawQuery(requete, null);
		//int i = 0 ;
		Log.i("GlobalDAO", "readTables - dictionary : id_dictionary;mot_langue_ref;phon_langue_ref;ordre"  );
		while (cursor.moveToNext()) {
			Log.i("GlobalDAO", cursor.getString(0) + ";" + cursor.getString(1)
					+ ";" + cursor.getString(2)
					+ ";" + cursor.getString(3)
					+ ";" + cursor.getString(4)
					);
		}
		Log.i("GlobalDAO", "readTables dictionary - fin"  );

		
		Log.i("GlobalDAO", "readTables - quizz_question : "  );
		requete = "select id_question" +
				",id_quizz " +
				",ordre" +
				",solution,phon_trad" +
				"  FROM quizz_question order by id_question";
		cursor = db.rawQuery(requete, null);
		while (cursor.moveToNext()) {
			Log.i("GlobalDAO", cursor.getString(0) + ";" + cursor.getString(1)
					+ ";" + cursor.getString(2)
					+ ";" + cursor.getString(3)
					+ ";" + cursor.getString(4)
					);
		}
		Log.i("GlobalDAO", "readTables quizz_question - fin"  );
		
		Log.i("GlobalDAO", "readTables - quizz "  );
		requete = "select " +
				"id_quizz " +
				",ordre" +
				",lib_quizz" +
				"  FROM quizz order by ordre";
		cursor = db.rawQuery(requete, null);
		while (cursor.moveToNext()) {
			Log.i("GlobalDAO", cursor.getString(0)
					+ ";" + cursor.getString(1)
					+ ";" + cursor.getString(2)
					);
		}
		Log.i("GlobalDAO", "readTables quizz - fin"  );
		
		//Log.i("GlobalDAO", "CREATE TABLE quizz_question");
		//db.execSQL("CREATE TABLE quizz_question (id_question INTEGER,id_quizz INTEGER, ordre INTEGER, proposition1 TEXT, proposition2 TEXT, proposition3 TEXT, proposition4 TEXT,solution TEXT, phon_trad TEXT  );");

		
		
		
//		SQLiteDatabase db = this.getWritableDatabase();
//		String requete = "select id_quizz, user ,  note  FROM quizz_result";
//		Cursor cursor = db.rawQuery(requete, null);
//		//int i = 0 ;
//		Log.i("GlobalDAO", "readTables - quizz_result"  );
//		while (cursor.moveToNext()) {
//			Log.i("GlobalDAO", cursor.getString(0) + ";" + cursor.getString(1) + ";" + cursor.getString(2)  );
//		}
//		Log.i("GlobalDAO", "readTables quizz_result - fin"  );
//		requete = "select num_dictionary,mot,prononciation   FROM dictionary order by num_dictionary";
//		cursor = db.rawQuery(requete, null);
//		//int i = 0 ;
//		Log.i("GlobalDAO", "readTables - dictionary"  );
//		while (cursor.moveToNext()) {
//			Log.i("GlobalDAO", cursor.getString(0) + ";" + cursor.getString(1) + ";" + cursor.getString(2)  );
//		}
//		Log.i("GlobalDAO", "readTables dictionary - fin"  );
//		
//		
//		cursor.close();
	}
	

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}


}
